﻿CREATE PROCEDURE [dbo].[spBuildSearchKeys]
	@CustomerID int
AS
	set nocount on
	delete from tblTextSearch where CustomerID = @CustomerID
	delete from tblIDSearch where CustomerID = @CustomerID

	declare @textSearch nvarchar(max)
	set @textSearch = ''

	-- Make a list of persons relevant to this customer
	declare @person table ( PersonID int )

	insert into @person
	select PersonID
	from tblCustomer 
	where CustomerID = @CustomerID

	insert into @person
	select ShipTo_PersonID
	from tblOrderHeader
	where CustomerID = @CustomerID

	declare C1 cursor local for select distinct PersonID from @person
	declare @PersonID int
	open C1
	fetch next from C1 into @PersonID
	while @@FETCH_STATUS = 0
	begin
		select @textSearch = @textSearch + ' ' + rtrim(name)
		from tblPerson
		where PersonID = @PersonID

		fetch next from C1 into @PersonID
	end
	close C1
	deallocate C1

	-- Make a list of addresses relevent to this customer
	declare @Address table ( AddressID int )

	insert into @Address
	select T2.AddressID
	from tblCustomer T1
		join tblPerson T2 on T1.PersonID = T2.PersonID
	where T1.CustomerID = @CustomerID

	insert into @Address
	select T2.AddressID
	from tblOrderHeader T1
		join tblPerson T2 on T1.ShipTo_PersonID = T2.PersonID
	where T1.CustomerID = @CustomerID

	insert into @Address
	select T1.ShipTo_AddressID
	from tblOrderHeader T1
	where T1.CustomerID = @CustomerID

	declare C2 cursor local for select distinct AddressID from @Address
	declare @AddressID int
	open C2
	fetch next from C2 into @AddressID
	while @@FETCH_STATUS = 0
	begin
		select @textSearch = @textSearch + rtrim(coalesce( ' ' + Address1, '' )) +
			rtrim(coalesce( ' ' + Address2, '' )) +
			rtrim(coalesce( ' ' + Address3, '' )) +
			rtrim(coalesce( ' ' + City, '' )) +
			rtrim(coalesce( ' ' + State, '' )) +
			rtrim(coalesce( ' ' + PostalCode, '' ))
		from tblAddress
		where AddressID = @AddressID

		fetch next from C2 into @AddressID
	end
	close C2
	deallocate C2

	-- Make a list of Contact Points relevent to this customer
	declare @ContactPoint table ( ContactPointID int )

	insert into @ContactPoint
	select T2.ContactPointID
	from @person T1
		join tblContactPoint T2 on T1.PersonID = T2.PersonID

	declare C3 cursor local for select distinct ContactPointID from @ContactPoint
	declare @ContactPointID int
	open C3
	fetch next from C3 into @ContactPointID
	while @@FETCH_STATUS = 0
	begin
		select @textSearch = @textSearch + rtrim(coalesce( ' ' + Value, '' ))
		from tblContactPoint
		where ContactPointID = @ContactPointID

		fetch next from C3 into @ContactPointID
	end
	close C3
	deallocate C3

	-- Create the new TextSearch record for this customer
	insert into tblTextSearch ( CustomerID, SearchKey )
	values ( @CustomerID, @textSearch )

	-- Create the IDSearch values for this customer
	insert into tblIDSearch ( CustomerID, IDValue, IDSearchKeyCode )
	values ( @CustomerID, @CustomerID, 'C' )

	insert into tblIDSearch ( CustomerID, IDValue, IDSearchKeyCode )
	select @CustomerID, OrderID, 'O'
	from tblOrderHeader 
	where CustomerID = @CustomerID

	insert into tblIDSearch ( CustomerID, IDValue, IDSearchKeyCode )
	select @CustomerID, IDValue, 'S'
	from tblCustomerSourceID
	where CustomerID = @CustomerID

	insert into tblIDSearch ( CustomerID, IDValue, IDSearchKeyCode )
	select @CustomerID, T1.IDValue, 'T'
	from tblOrderSourceID T1
		join tblOrderHeader T2 on T1.OrderID = T2.OrderID
	where T2.CustomerID = @CustomerID

	insert into tblIDSearch ( CustomerID, IDValue, IDSearchKeyCode )
	select distinct @CustomerID, T1.PackageID, 'P'
	from tblInventoryTransaction T1
		join tblOrderHeader T2 on T1.OrderID = T2.OrderID
	where T2.CustomerID = @CustomerID
		and T1.PackageID is not null

	insert into tblIDSearch ( CustomerID, IDValue, IDSearchKeyCode )
	select distinct @CustomerID, T1.RMAReturnID, 'R'
	from tblRMAReturn T1
		join tblOrderHeader T2 on T1.OrderID = T2.OrderID
	where T2.CustomerID = @CustomerID
RETURN 0